/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package inet.dao;

import inet.entity.Employee;
import inet.entity.Position;
import inet.util.Md5;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author MiSoKo
 */
public class EmployeeDAO extends BaseDAO {

//Show nhân viên
    public List<Employee> getAllEmployee(String name, BigDecimal branchid, BigDecimal depid) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        List<Employee> emps = null;
        Vector<String> vParam = new Vector<String>();
        try {
            sql = " SELECT POSITION.EMP_ID,EMP_CODE,FIRST_NAME,LAST_NAME,EMP_MOBILE,EMP_EMAIL,ADDRESS,EMP_DOB,BIRTH_PLACE,"
                    + "GENDER,NATIONALITY,LITERACY,ROLL,ACC_NAME,STATUS,AVATAR FROM EMPLOYEE , POSITION "
                    + "WHERE POSITION.EMP_ID = EMPLOYEE.EMP_ID";
            if (name != null && !"".equals(name)) {
                sql += " AND UPPER(A.LAST_NAME) LIKE '%'|| UPPER(?) ||'%'";
                vParam.add(name);
            }
            if (branchid != null) {
                sql += " AND POSITION.BRANCH_ID= ?";
                vParam.add(branchid.toString());
            }
            if (depid != null) {
                sql += " AND POSITION.DEP_ID = ?";
                vParam.add(depid.toString());
            }
            sql += "  AND STATUS = 0 ORDER BY EMP_ID DESC";
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            int cout = 1;
            for (String param : vParam) {
                ps.setString(cout, param);
                cout++;
            }
            rs = ps.executeQuery();
            Employee emp = null;
            while (rs.next()) {
                emp = new Employee();
                if (emps == null) {
                    emps = new ArrayList<Employee>();
                }
                int i = 1;
                emp.setEmp_id(rs.getBigDecimal(i++));
                emp.setEmp_code(rs.getString(i++));
                emp.setFirst_name(rs.getString(i++));
                emp.setLast_name(rs.getString(i++));
                emp.setEmp_mobile(rs.getString(i++));
                emp.setEmp_email(rs.getString(i++));
                emp.setAddress(rs.getString(i++));
                emp.setEmp_dob(rs.getTimestamp(i++));
                emp.setBirth_place(rs.getString(i++));
                emp.setGender(rs.getInt(i++));
                emp.setNationality(rs.getString(i++));
                emp.setLyteracy(rs.getString(i++));
                emp.setRoll(rs.getInt(i++));
                emp.setAcc_name(rs.getString(i++));
                emp.setStatus(rs.getInt(i++));
                emps.add(emp);
            }
            return emps;
        } catch (Exception ex) {
            System.out.println("Error in sql: " + sql);
            ex.printStackTrace();
        } finally {
            releaseConnection(conn, ps, rs);
        }
        return null;
    }
    //danh sách nhan viên chấm công theo phòng ban trung tâm

    public List<Employee> getAllEMP() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        List<Employee> lstemp = null;
        try {
            sql = "SELECT EMPLOYEE.EMP_CODE, EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME "
                    + "FROM POSITION, EMPLOYEE "
                    + "WHERE EMPLOYEE.EMP_ID = POSITION.EMP_ID AND POSITION.DEP_ID= 1 AND POSITION.BRANCH_ID= 1;";
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            Employee emp = null;
            while (rs.next()) {
                emp = new Employee();
                if (lstemp == null) {
                    lstemp = new ArrayList<Employee>();
                }
                int i = 1;
                emp.setEmp_code(rs.getString(i++));
                emp.setFirst_name(rs.getString(i++));
                emp.setLast_name(rs.getString(i++));
                lstemp.add(emp);
            }
            return lstemp;
        } catch (Exception ex) {
            System.out.println("Error in sql: " + sql);
            ex.printStackTrace();
        } finally {
            releaseConnection(conn, ps, rs);
        }
        return null;
    }

    //check Mã Nhân viên
    public int checkEmpCode(String manv) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;

        try {
            sql = "SELECT COUNT(EMP_CODE) FROM EMPLOYEE WHERE EMP_CODE=? ";
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, manv);
            rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception ex) {
            System.out.println("Error in sql: " + sql);
            ex.printStackTrace();
        } finally {
            releaseConnection(conn, ps, rs);
        }
        return 0;
    }

    //Check acount
    public int checkAcount(String acount) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;

        try {
            sql = "SELECT COUNT(ACC_NAME) FROM EMPLOYEE WHERE ACC_NAME=? ";
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, acount);
            rs = ps.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception ex) {
            System.out.println("Error in sql: " + sql);
            ex.printStackTrace();
        } finally {
            releaseConnection(conn, ps, rs);
        }
        return 0;
    }

    //Ẩn nhân viên
    public boolean updateStatusEmp(BigDecimal empid) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            String sql = "UPDATE EMPLOYEE SET STATUS = ? WHERE EMP_ID = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, 1);
            ps.setBigDecimal(2, empid);
            return ps.executeUpdate() == 1;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            releaseConnection(conn, ps);
        }
        return false;
    }

    //Test commit.
    public boolean addEmpCommit(Employee emps, Position position) {
        Connection conn = null;
        PreparedStatement insertEmp = null;
        PreparedStatement insertPosi = null;
        PreparedStatement ps = null;

        String sqlEmp = "INSERT INTO EMPLOYEE(EMP_CODE, FIRST_NAME, LAST_NAME, EMP_MOBILE, EMP_EMAIL, ADDRESS, "
                + "EMP_DOB, BIRTH_PLACE, GENDER, NATIONALITY, LITERACY, ROLL, ACC_NAME, PASSWORD, STATUS, AVATAR)"
                + "   VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        String sqlEmpID = "SELECT MAX(EMP_ID) AS EMPID FROM EMPLOYEE";

        String sqlPosi = "INSERT INTO POSITION (POSITION_ID, EMP_ID, COM_ID, COM_POSITION, "
                + "BRANCH_ID, BRANCH_POSITION, DEP_ID, DEP_POSITION) "
                + "VALUES(POSITION_SEQ.NEXTVAL, ?, ?, ?, ?, ?, ?, ?)";

        try {
            conn = getConnection();
            conn.setAutoCommit(false);
            ResultSet rs = null;
            insertEmp = conn.prepareStatement(sqlEmp);
            ps = conn.prepareStatement(sqlEmpID);
            insertPosi = conn.prepareStatement(sqlPosi);
            int i = 1;
            int j = 1;

            insertEmp.setString(i++, emps.getEmp_code());
            insertEmp.setString(i++, emps.getFirst_name());
            insertEmp.setString(i++, emps.getLast_name());
            insertEmp.setString(i++, emps.getEmp_mobile());
            insertEmp.setString(i++, emps.getEmp_email());
            insertEmp.setString(i++, emps.getAddress());
            insertEmp.setTimestamp(i++, (Timestamp) emps.getEmp_dob());
            insertEmp.setString(i++, emps.getBirth_place());
            insertEmp.setInt(i++, emps.getGender());
            insertEmp.setString(i++, emps.getNationality());
            insertEmp.setString(i++, emps.getLyteracy());
            insertEmp.setInt(i++, emps.getRoll());
            insertEmp.setString(i++, emps.getAcc_name());
            insertEmp.setString(i++, Md5.Hash(emps.getPassword()));
            insertEmp.setInt(i++, emps.getStatus());
            insertEmp.setString(i++, emps.getAvatar());
            insertEmp.executeUpdate();
            rs = ps.executeQuery();
            if (rs.next()) {
                position.setEmp_id(BigDecimal.valueOf(rs.getInt(1)));
            }
            insertPosi.setBigDecimal(j++, position.getEmp_id());
            insertPosi.setBigDecimal(j++, position.getCom_id());
            insertPosi.setString(j++, position.getCom_position());
            insertPosi.setBigDecimal(j++, position.getBranch_id());
            insertPosi.setString(j++, position.getBranch_position());
            insertPosi.setBigDecimal(j++, position.getDep_id());
            insertPosi.setString(j++, position.getDep_position());
            insertPosi.executeUpdate();

            conn.commit();

        } catch (Exception e) {
            e.printStackTrace();
            if (conn != null) {
                try {
                    System.err.print("Bạn chưa làm đc gì");
                    conn.rollback();
                } catch (Exception excep) {
                    excep.printStackTrace();
                }
            }
        } finally {
            if (insertEmp != null) {
                try {
                    insertEmp.close();
                } catch (SQLException ex) {
                    Logger.getLogger(EmployeeDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException ex) {
                    Logger.getLogger(EmployeeDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if (insertPosi != null) {
                try {
                    insertPosi.close();
                } catch (SQLException ex) {
                    Logger.getLogger(EmployeeDAO.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            try {
                conn.setAutoCommit(true);
            } catch (SQLException ex) {
                Logger.getLogger(EmployeeDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return false;
    }

//    public static void main(String[] a) {
//        EmployeeDAO d = new EmployeeDAO();
//        Position posi = new Position();
//        posi.setCom_id(BigDecimal.valueOf(1));
//        posi.setCom_position("Tổng giám đốc");
//        d.addEmpCommit(new Employee(BigDecimal.valueOf(15), "123", "123", "123", "123", "123", "123", null, null, 0, null, null, 1, null, null, 0, null), posi);
//    }
}
